package com.example.mylibrary.repository.impl;

import com.example.mylibrary.entity.Book;
import com.example.mylibrary.entity.Borrow;
import com.example.mylibrary.entity.Reader;
import com.example.mylibrary.repository.BorrowRepository;
import com.example.mylibrary.utils.JDBCTools;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BorrowRepositoryImpl implements BorrowRepository {

    @Override
    public void insert(Integer bookid, Integer readerid, String borrowTime, String returnTime, Integer adminid, Integer state) {
        Connection connection = JDBCTools.getConnection();
        String sql = "insert into borrow(bookid,readerid,borrowTime,returnTime,state) values(?,?,?,?,0)";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, bookid);
            preparedStatement.setInt(2, readerid);
            preparedStatement.setString(3, borrowTime);
            preparedStatement.setString(4, returnTime);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(connection, preparedStatement, null);
        }
    }

    @Override
    public List<Borrow> findAllByReaderId(Integer readerid,Integer index,Integer limt) {
        Connection connection = JDBCTools.getConnection();
        String sql = "select borrow.id,book.name,book.author,book.publish,borrow.borrowtime,borrow.returntime,reader.name,reader.tel,reader.cardid,borrow.state from book,reader,borrow where readerid = ? and book.id = borrow.bookid and reader.id = borrow.readerid limit ?,?";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Borrow> list = new ArrayList<>();
        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1, readerid);
            statement.setInt(2, index);
            statement.setInt(3, limt);

            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                //取出所有查询到的内容
                //int borrowId = resultSet.getInt(1);
                //String bookName = resultSet.getString(2);
                //String author = resultSet.getString(3);
                //String publish = resultSet.getString(4);
                //String borrowTime = resultSet.getString(5);
                //String returnTime = resultSet.getString(6);
                //String readerName = resultSet.getString(7);
                //String tel = resultSet.getString(8);
                //String cardId = resultSet.getString(9);
                ////封装
                //Book book = new Book(bookName, author, publish);
                //Reader reader = new Reader(readerName, tel, cardId);
                //Borrow borrow = new Borrow(borrowId, book, reader, borrowTime, returnTime);
                //list.add(borrow);

                list.add(new Borrow(resultSet.getInt(1),
                        new Book(resultSet.getString(2), resultSet.getString(3), resultSet.getString(5)),
                        new Reader(resultSet.getString(7), resultSet.getString(8), resultSet.getString(9)),
                        resultSet.getString(5),
                        resultSet.getString(6), resultSet.getInt(10)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTools.release(connection, statement, resultSet);
        }
        return list;
    }

    @Override
    public int count(Integer readerid) {
        Connection connection = JDBCTools.getConnection();
        String sql = "select count(*) from book,reader,borrow where readerid = ? and book.id = borrow.bookid and reader.id = borrow.readerid";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        int count = 0;
        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1, readerid);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                count = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTools.release(connection, statement, resultSet);
        }
        return count;
    }

    @Override
    public List<Borrow> findAllBorrowByState(Integer state,Integer index,Integer limt) {
        Connection connection = JDBCTools.getConnection();
        String sql = "select borrow.id,book.name,book.author,book.publish,borrow.borrowtime,borrow.returntime,reader.name,reader.tel,reader.cardid,borrow.state from book,reader,borrow where state = ? and book.id = borrow.bookid and reader.id = borrow.readerid limit ?,?";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Borrow> list = new ArrayList<>();
        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1, state);
            statement.setInt(2, index);
            statement.setInt(3, limt);

            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(new Borrow(resultSet.getInt(1),
                        new Book(resultSet.getString(2), resultSet.getString(3), resultSet.getString(5)),
                        new Reader(resultSet.getString(7), resultSet.getString(8), resultSet.getString(9)),
                        resultSet.getString(5),
                        resultSet.getString(6), resultSet.getInt(10)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTools.release(connection, statement, resultSet);
        }
        return list;
    }

    @Override
    public int countByState(Integer state) {
        Connection connection = JDBCTools.getConnection();
        String sql = "select count(*) from book,reader,borrow where state = ? and book.id = borrow.bookid and reader.id = borrow.readerid";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        int count = 0;
        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1, state);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                count = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTools.release(connection, statement, resultSet);
        }
        return count;
    }

    @Override
    public void handle(Integer borrowid, Integer state, Integer adminid) {
        Connection connection = JDBCTools.getConnection();
        String sql = "update borrow set state = ?,adminid = ? where id = ?";
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1, state);
            statement.setInt(2, adminid);
            statement.setInt(3, borrowid);
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTools.release(connection, statement, null);
        }

    }
}
